<?php
namespace app\dao;

use yii\db\Query;
use app\dao\BaseDAO;
use app\enum\EnumOther;
use app\enum\EnumOriginType;
use app\helpers\Utility;
use yii\base\Object;
use yii\web\GroupUrlRule;
use Yii;
/**
 * @desc transaction 表操作类
 * @author liaojianwen
 * @date 2016年11月22日下午4:48:56
 */
class TransactionDAO extends BaseDAO
{
	/**
	 * @desc 构造函数
	 * @author liaojianwen
	 * @date 2016-11-22
	 */
	public function __construct()
	{
		parent::__construct();
		$this->_table = 'transaction';
		$this->_pKey ='transaction_id';
	}
	
	/**
	 * @inheritdoc
	 */
	public static function tableName()
	{
		return '{{%transaction}}';
	}
	
	/**
	 * @desc 获取出入库明细列表
	 * @param $cond 查询条件
	 * @param $pageInfo 页面数据
	 * @author liaojianwen
	 * @date 2016-12-22
	 */
	public function getTransactions($cond, $pageInfo)
	{
		$limit = $pageInfo ['pageSize'];
		$offset = ($pageInfo ['page'] - 1) * $limit;
		$selects = "transaction_id,goods_id,t.quantity,t.warehouse_id,origin_type,origin_id,origin_line_id,origin_time,flag,warehouse_name,
				product_name,supplier_name";
		
		
		$conditions = "t.delete_flag = :flag";
		$params  =[
				':flag'=>EnumOther::NO_DELETE,
		];
		if(isset($cond['vendor']) && !empty($cond['vendor'])){
			$conditions .=" and t.vendor_id =:vid";
			$params[':vid'] =$cond['vendor'];
		}
		if(isset($cond['warehouse']) && !empty($cond['warehouse'])){
			$conditions .=" and t.warehouse_id =:wid";
			$params[':wid'] = $cond['warehouse'];
		}
		
		if(isset($cond['customer']) && !empty($cond['customer'])){
			$conditions .= " and t.customer_id =:cid";
			$params[':cid'] = $cond['customer'];
		}
		$query = new Query();
		$query->select($selects)
			->from("$this->_table t")
			->leftJoin("product p","p.product_id = t.goods_id")
			->leftJoin("warehouse w","w.warehouse_id = t.warehouse_id")
			->leftJoin("supplier s","s.supplier_id = t.vendor_id")
			->where($conditions, $params)
			->orderBy ( ['t.create_time'=>SORT_ASC]);
		
		if(isset($cond['starTime']) && !empty($cond['starTime'])){
			$query->andwhere(['between','t.origin_time',strtotime($cond['starTime']), strtotime($cond['endTime'])]);
		}
		
		if(isset($cond['name']) && !empty($cond['name'])){
			$query->andWhere(['like','p.product_name', $cond['name']]);
		}
		$result ['count'] = $query->count ();
		$result ['list'] = $query->offset ( $offset )->limit ( $limit )->all ();
		foreach ($result['list'] as &$item)
		{
			if($item['flag'] == 1){
				//入库
				$item['in_quantity'] = $item['quantity'];
				$item['out_quantity'] = 0;
				$item['minus'] = $item['quantity'];
			} else {
				//出库
				$item['out_quantity'] = $item['quantity'];
				$item['in_quantity'] = 0;
				$item['minus'] = $item['quantity'];
			}
			$item['type'] = Utility::getArrayValue(EnumOriginType::$origin_type, $item['origin_type']);
			
		}
		$result ['page'] = array (
				'page' => $pageInfo ['page'],
				'pageSize' => $pageInfo ['pageSize']
		);
		return $result;
		
	}
	
	/**
	 * @desc 出入库汇总统计
	 * @author liaojianwen
	 * @date 2017-04-20
	 */
	public function getStockSum($cond, $pageInfo)
	{
		
		$limit = $pageInfo ['pageSize'];
		$offset = ($pageInfo ['page'] - 1) * $limit;
		
		$res_ware = WarehouseDAO::getInstance()->findByAttributes("warehouse_id","is_scrapped = :sid",[':sid' => EnumOther::SCRAPPED]);
		if($res_ware){
			$scrapped_ware = $res_ware['warehouse_id'];
		}
// 		$selections = 'goods_id,sum(if(flag = 0,t.quantity,0)) as out_quantity, sum(if(flag =1,t.quantity,0)) as in_quantity ,product_name';
// 		$conditions = "t.delete_flag = :flag";
// 		$params  =[
// 				':flag'=>EnumOther::NO_DELETE,
// 		];
		
		$sql = "select goods_id , sum(if(flag = 0 and origin_type <> ".EnumOriginType::origin_transfer.", t.quantity,0)) as out_quantity,
		sum(if(flag = 1 and origin_type <> ".EnumOriginType::origin_transfer.",t.quantity,0)) as in_quantity,product_name,p.quantity,
		sum(if(t.warehouse_id = ".$scrapped_ware." || origin_type = ".EnumOriginType::origin_pk_in." , t.quantity, 0)) bad_quantity, 
		sum(if(origin_type = ".EnumOriginType::origin_eat.", t.quantity, 0)) as eat_quantity,
		(select  init_num from `transaction` where goods_id = t.goods_id and origin_time between ".$cond['starTime']." and ".$cond['endTime']." LIMIT 1 ) as init_num
		from {$this->_table} t left join product p on t.goods_id = p.product_id where t.delete_flag =".EnumOther::NO_DELETE ;
		if(isset($cond['starTime']) && !empty($cond['starTime'])){
			$sql .= " and origin_time between ".$cond['starTime'] .' and '. $cond['endTime'];
		}		
		
		
		if(isset($cond['name']) && !empty($cond['name'])){
			$sql .= " and p.product_name like '%".$cond['name'] ."%'";
		}
		$sql .=" group by t.goods_id order by goods_id ";
		$command = Yii::$app->db;
		$count = $command->createCommand()->setSql($sql)->execute();
		
		$sql .="  limit ".$limit .' offset '. $offset ;
		
		$return = $command->createCommand($sql)->queryAll();
		
		$result ['count'] = $count;
		$result ['list'] = $return;
		
		$result ['page'] = array (
				'page' => $pageInfo ['page'],
				'pageSize' => $pageInfo ['pageSize']
		);
		
		return $result;
	}
	
	
	
}